---
title: 'Astro DB'
description: Learn how to use Astro DB, a fully-managed SQL database designed exclusively for Astro.
githubIntegrationURL: 'https://github.com/withastro/astro/tree/main/packages/db/'
i18nReady: true
---
import { FileTree } from '@astrojs/starlight/components';
import PackageManagerTabs from '~/components/tabs/PackageManagerTabs.astro';
import ReadMore from '~/components/ReadMore.astro';
import StudioHeading from '~/components/StudioHeading.astro';
import { Steps } from '@astrojs/starlight/components';

Astro DB is a fully-managed SQL database designed exclusively for Astro. Develop locally or connect to a hosted database managed on our [Astro Studio](/en/recipes/studio/) platform.

## Installation

Add Astro DB to a new or existing Astro project (requires `astro@4.5` or later) with the [`@astrojs/db` integration](/en/guides/integrations-guide/db/) (`v0.8.1` or later). Astro includes a built-in `astro add` command to automate this setup process for you.

<PackageManagerTabs>
  <Fragment slot="npm">
  ```sh
  npx astro add db
  ```
  </Fragment>
  <Fragment slot="pnpm">
  ```sh
  pnpm astro add db
  ```
  </Fragment>
  <Fragment slot="yarn">
  ```sh
  yarn astro add db
  ```
  </Fragment>
</PackageManagerTabs>

If you prefer, you can [install `@astrojs/db` manually](/en/guides/integrations-guide/db/#manual-installation) instead.

## Define your database

Astro DB is a complete solution to configuring, developing and querying your data. A local database is created whenever you run `astro dev`, using LibSQL to manage your data without the need for Docker or a network connection.

Installing `@astrojs/db` with the `astro add` command will create a `db/config.ts` file in your project where you will define your databases tables:

```ts title="db/config.ts"
import { defineDb } from 'astro:db';

export default defineDb({
  tables: { },
})
```

### Tables

Data in Astro DB is stored using SQL tables. Tables structure your data into rows and columns, where columns enforce the type of each row value.

When you define a table, Astro will generate a TypeScript interface to query that table from your project. The result is full TypeScript support when you access your data with property autocompletion and type-checking.

To configure a database table, import and use the `defineTable()` and `column` utilities from `astro:db`.

This example configures a `Comment` table with required text columns for `author` and `body`. Then, make it available to your project through the `defineDb()` export.

```ts title="db/config.ts" "Comment"
import { defineDb, defineTable, column } from 'astro:db';

const Comment = defineTable({
  columns: {
    author: column.text(),
    body: column.text(),
  }
})

export default defineDb({
  tables: { Comment },
})
```

<ReadMore>See the [table configuration reference](/en/guides/integrations-guide/db/#table-configuration-reference) for a complete reference of table options.</ReadMore>

### Columns

Astro DB supports the following column types:

```ts title="db/config.ts" "column.text()" "column.number()" "column.boolean()" "column.date()" "column.json()"
import { defineTable, column } from 'astro:db';

const Comment = defineTable({
  columns: {
    // A string of text.
    author: column.text(),
    // A whole integer value.
    likes: column.number(),
    // A true or false value.
    flagged: column.boolean(),
    // Date/time values queried as JavaScript Date objects.
    published: column.date(),
    // An untyped JSON object.
    metadata: column.json(),
  }
});
```

<ReadMore>See the [table columns reference](/en/guides/integrations-guide/db/#table-configuration-reference) for more details.</ReadMore>

### Table References

Relationships between tables are a common pattern in database design. For example, a `Blog` table may be closely related to other tables of `Comment`, `Author`, and `Category`.

You can define these relations between tables and save them into your database schema using **reference columns**. To establish a relationship, you will need:

- An **identifier column** on the referenced table. This is usually an `id` column with the `primaryKey` property.
- A column on the base table to **store the referenced `id`**. This uses the `references` property establish a relationship.

This example shows a `Comment` table's `authorId` column referencing an `Author` table's `id` column.

```ts title="db/config.ts" {3, 10}
const Author = defineTable({
  columns: {
    id: column.number({ primaryKey: true }),
    name: column.text(),
  }
});

const Comment = defineTable({
  columns: {
    authorId: column.number({ references: () => Author.columns.id }),
    content: column.text(),
  }
});
```

## Seed your database

In development, Astro will use your DB config to generate local types according to your schemas. These will be generated fresh each time the dev server is started, and will allow you to query and work with the shape of your data with type safety and autocompletion.

To seed development data for testing and debugging into your Astro project, create a `db/seed.ts` file. Import both the `db` object and any configured table from `astro:db`. Use the `db.insert()` function to provide an array of table row data objects.

The following example defines two rows of development data for a `Comment` table:

```ts title="db/seed.ts"
import { db, Comment } from 'astro:db';

export default async function() {
  await db.insert(Comment).values([
    { authorId: 1, body: 'Hope you like Astro DB!' },
    { authorId: 2, body: 'Enjoy!'},
  ])
}
```

Your development server will automatically restart your database whenever this file changes, regenerating your types and seeding your development data from `seed.ts`.

## Query your database

You can query your database from any [Astro page](/en/basics/astro-pages/#astro-pages) or [endpoint](/en/guides/endpoints/) in your project using the provided `db` ORM and query builder.

### Drizzle ORM

```
import { db } from 'astro:db';
```

Astro DB includes a built-in [Drizzle ORM](https://orm.drizzle.team/) client. There is no setup or manual configuration required to use the client. The Astro DB `db` client is automatically configured to talk to your database (local or remote) when you run Astro. It uses your exact database schema definition for type-safe SQL queries with TypeScript errors when you reference a column or table that doesn't exist.


### Select 

The following example selects all rows of a `Comment` table. This returns the complete array of seeded development data from `db/seed.ts` which is then available for use in your page template:

```astro title="src/pages/index.astro"
---
import { db, Comment } from 'astro:db';

const comments = await db.select().from(Comment);
---

<h2>Comments</h2>

{
  comments.map(({ author, body }) => (
    <article>
      <p>Author: {author}</p>
      <p>{body}</p>
    </article>
  ))
}
```

<ReadMore>See the [Drizzle `select()` API reference](https://orm.drizzle.team/docs/select) for a complete overview.</ReadMore>

### Insert

To accept user input, such as handling form requests and inserting data into your remote hosted database, configure your Astro project for [on-demand rendering](/en/basics/rendering-modes/#on-demand-rendered) and [add an SSR adapter](/en/guides/server-side-rendering/#official-adapters) for your deployment environment.

This example inserts a row into a `Comment` table based on a parsed form POST request:

```astro
---
// src/pages/index.astro
import { db, Comment } from 'astro:db';

if (Astro.request.method === 'POST') {
  // parse form data
  const formData = await Astro.request.formData();
  const author = formData.get('author');
  const content = formData.get('content');
  if (typeof author === 'string' && typeof content === 'string') {
    // insert form data into the Comment table
    await db.insert(Comment).values({ author, content });
  }
}

// render the new list of comments on each request
const comments = await db.select().from(Comment);
---

<form method="POST" style="display: grid">
	<label for="author">Author</label>
	<input id="author" name="author" />

	<label for="content">Content</label>
	<textarea id="content" name="content"></textarea>

	<button type="submit">Submit</button>
</form>

<!--render `comments`-->
```

You can also query your database from an API endpoint. This example deletes a row from a `Comment` table by the `id` param:

```ts
// src/pages/api/comments/[id].ts
import type { APIRoute } from "astro";
import { db, Comment, eq } from 'astro:db';

export const DELETE: APIRoute = async (ctx) => {
  await db.delete(Comment).where(eq(Comment.id, ctx.params.id ));
  return new Response(null, { status: 204 });
}
```

<ReadMore>

See the [Drizzle `insert()` API reference](https://orm.drizzle.team/docs/insert) for a complete overview.

</ReadMore>

### Filtering

To query for table results by a specific property, use [Drizzle options for partial selects](https://orm.drizzle.team/docs/select#partial-select). For example, add [a `.where()` call](https://orm.drizzle.team/docs/select#filtering) to your `select()` query and pass the comparison you want to make. 

The following example queries for all rows in a `Comment` table that contain the phrase "Astro DB." Use [the `like()` operator](https://orm.drizzle.team/docs/operators#like) to check if a phrase is present within the `body`:


```astro title="src/pages/index.astro"
---
import { db, Comment, like } from 'astro:db';

const comments = await db.select().from(Comment).where(
    like(Comment.body, '%Astro DB%')
);
---
```

### Drizzle utilities

All Drizzle utilities for building queries are exposed from the `astro:db` module. This includes:

- [Filter operators](https://orm.drizzle.team/docs/operators) like `eq()` and `gt()`
- [Aggregation helpers](https://orm.drizzle.team/docs/select#aggregations-helpers) like `count()`
- [The `sql` helper](https://orm.drizzle.team/docs/sql) for writing raw SQL queries

```ts
import { eq, gt, count, sql } from 'astro:db';
```

### Relationships

You can query related data from multiple tables using a SQL join. To create a join query, extend your `db.select()` statement with a join operator. Each function accepts a table to join with and a condition to match rows between the two tables.

This example uses an `innerJoin()` function to join `Comment` authors with their related `Author` information based on the `authorId` column. This returns an array of objects with each `Author` and `Comment` row as top-level properties:

```astro title="src/pages/index.astro"
---
import { db, eq, Comment, Author } from 'astro:db';

const comments = await db.select()
  .from(Comment)
  .innerJoin(Author, eq(Comment.authorId, Author.id));
---

<h2>Comments</h2>

{
  comments.map(({ Author, Comment }) => (
    <article>
      <p>Author: {Author.name}</p>
      <p>{Comment.body}</p>
    </article>
  ))
}
```

<ReadMore>

See the [Drizzle join reference](https://orm.drizzle.team/docs/joins#join-types) for all available join operators and config options.

</ReadMore>

### Batch Transactions

All remote database queries are made as a network request. You may need to "batch" queries together into a single transaction when making a large number of queries, or to have automatic rollbacks if any query fails.

This example seeds multiple rows in a single request using the `db.batch()` method:

```ts
// db/seed.ts
import { db, Author, Comment } from 'astro:db';

export default async function () {
  const queries = [];
  // Seed 100 sample comments into your remote database
  // with a single network request.
  for (let i = 0; i < 100; i++) {
    queries.push(db.insert(Comment).values({ body: `Test comment ${i}` }));
  }
  await db.batch(queries);
}
```

<ReadMore>

See the [Drizzle `db.batch()`](https://orm.drizzle.team/docs/batch-api) docs for more details.

</ReadMore>

<StudioHeading>
## Astro Studio
</StudioHeading>

Astro DB can [connect to the Astro Studio platform](/en/recipes/studio/) to quickly add a hosted database to your project. You can view, manage and deploy new hosted databases all from the Astro Studio dashboard.

To create a new project, you can use a [ready-made template](https://studio.astro.build) or visit the [Astro Studio guide](/en/recipes/studio/#create-a-new-studio-project).

<StudioHeading>
### Pushing table schemas
</StudioHeading>


Your table schema will change overtime as your project grows. You can safely test configuration changes locally and push to your Studio database when you deploy.

When [creating a Studio project from the dashboard](#astro-studio), you will have the option to create a GitHub CI action. This will automatically migrate schema changes when merging with your repository's main branch.

You can also push your local schema changes to Astro Studio via the CLI using the `astro db push --remote` command:

<PackageManagerTabs>
  <Fragment slot="npm">
  ```sh
  npm run astro db push --remote
  ```
  </Fragment>
  <Fragment slot="pnpm">
  ```sh
  pnpm astro db push --remote
  ```
  </Fragment>
  <Fragment slot="yarn">
  ```sh
  yarn astro db push --remote
  ```
  </Fragment>
</PackageManagerTabs>

This command will verify that your local changes can be made without data loss and, if necessary, suggest how to safely make changes to your schema in order to resolve conflicts.

#### Pushing breaking schema changes

:::caution
__This will destroy your database__. Only perform this command if you do not need your production data.
:::

If you must change your table schema in a way that is incompatible with your existing data hosted at Astro Studio, you will need to reset your production database.

To push a table schema update that includes a breaking change, add the `--force-reset` flag to reset all production data:

<PackageManagerTabs>
  <Fragment slot="npm">
  ```sh
  npm run astro db push --remote --force-reset
  ```
  </Fragment>
  <Fragment slot="pnpm">
  ```sh
  pnpm astro db push --remote --force-reset
  ```
  </Fragment>
  <Fragment slot="yarn">
  ```sh
  yarn astro db push --remote --force-reset
  ```
  </Fragment>
</PackageManagerTabs>

<StudioHeading>
### Renaming tables
</StudioHeading>

It is possible to rename a table after pushing your schema to Astro Studio.

If you **do not have any important production data**, then you can [reset your database](#pushing-breaking-schema-changes) using the `--force-reset` flag. This flag will drop all of the tables in the database and create new ones so that it matches your current schema exactly.

To rename a table while preserving your production data, you must perform a series of non-breaking changes to push your local schema to Astro studio safely.

The following example renames a table from `Comment` to `Feedback`:

<Steps>

1. In your database config file, add the `deprecated: true` property to the table you want to rename:

    ```ts title="db/config.ts" ins={2}
    const Comment = defineTable({
      deprecated: true,
    	columns: {
    		author: column.text(),
    		body: column.text(),
  		}
    });
    ```

2. Add a new table schema (matching the existing table's properties exactly) with the new name:

	  ```ts title="db/config.ts" ins={9-14}
    const Comment = defineTable({
        deprecated: true,
    	columns: {
    		author: column.text(),
    		body: column.text(),
  		}
    });

	  const Feedback = defineTable({
        columns: {
          author: column.text(),
          body: column.text(),
        }
    });
    ```
3. [Push to Astro Studio](#pushing-table-schemas) with `astro db push --remote`. This will add the new table and mark the old as deprecated.
4. Update any of your local project code to use the new table instead of the old table. You might need to migrate data to the new table as well.
5. Once you are confident that the old table is no longer used in your project, you can remove the schema from your `config.ts`:
		```ts title="db/config.ts" del={1-7}
    const Comment = defineTable({
          deprecated: true,
    	  columns: {
    		  author: column.text(),
    		  body: column.text(),
  		  }
    });

	  const Feedback = defineTable({
          columns: {
            author: column.text(),
            body: column.text(),
          }
    });
    ```
6. Push to Astro Studio again with `astro db push --remote`. The old table will be dropped, leaving only the new, renamed table.
</Steps>

<StudioHeading>
### Pushing data 
</StudioHeading>

You may need to push data to your Studio database for seeding or data migrations. You can author a `.ts` file with the `astro:db` module to write type-safe queries. Then, execute the file against your Studio database using the command `astro db execute <file-path> --remote`:

The following Comments can be seeded using the command `astro db execute db/seed.ts --remote`:

```ts
// db/seed.ts
import { Comment } from 'astro:db';

export default async function () {
  await db.insert(Comment).values([
    { authorId: 1, body: 'Hope you like Astro DB!' },
    { authorId: 2, body: 'Enjoy!' },
  ])
}
```

<ReadMore>

See the [CLI reference](/en/guides/integrations-guide/db/#astro-db-cli-reference) for a complete list of commands.

</ReadMore>

<StudioHeading>
### Connect to Astro Studio
</StudioHeading>

By default, Astro will use a local database file whenever you run the `dev` or `build` commands. Tables are recreated from scratch when each command is run, and development seed data will be inserted.

To connect to your hosted Studio database, you can add the `--remote` flag. Use this flag for production deploys to have both readable and writable access to your Studio database. This will allow you to [accept and persist user data](#insert).

```bash
# Build with a remote connection
astro build --remote

# Develop with a remote connection
astro dev --remote
```

:::caution

Be careful using `--remote` in development. This will connect to a live production database, and all inserts, updates, or deletions will be persisted.

:::

To use a remote connection, you will need an app token to authenticate with Studio. Visit the Studio dashboard for token creation and setup instructions.

<ReadMore>

When you're ready to deploy, see our [Deploy with a Studio Connection guide](/en/recipes/studio/#deploy-with-a-studio-connection).

</ReadMore>

## Building Astro DB integrations

[Astro integrations](/en/reference/integrations-reference/) can extend user projects with additional Astro DB tables and seed data.

Use the `extendDb()` method in the `astro:db:setup` hook to register additional Astro DB config and seed files.
The `defineDbIntegration()` helper provides TypeScript support and auto-complete for the `astro:db:setup` hook.

```js {8-13}
// my-integration/index.ts
import { defineDbIntegration } from '@astrojs/db/utils';

export default function MyIntegration() {
  return defineDbIntegration({
    name: 'my-astro-db-powered-integration',
    hooks: {
      'astro:db:setup': ({ extendDb }) => {
        extendDb({
          configEntrypoint: '@astronaut/my-package/config',
          seedEntrypoint: '@astronaut/my-package/seed',
        });
      },
      // Other integration hooks...
    },
  });
}
```

Integration [config](#define-your-database) and [seed](#seed-your-database) files follow the same format as their user-defined equivalents.

### Type safe operations in integrations

While working on integrations, you may not be able to benefit from Astro’s generated table types exported from `astro:db`.
For full type safety, use the `asDrizzleTable()` utility to create a table reference object you can use for database operations.

For example, given an integration setting up the following `Pets` database table:

```js
// my-integration/config.ts
import { defineDb, defineTable, column } from 'astro:db';

export const Pets = defineTable({
  columns: {
    name: column.text(),
    species: column.text(),
  },
});

export default defineDb({ tables: { Pets } });
```

The seed file can import `Pets` and use `asDrizzleTable()` to insert rows into your table with type checking:

```js {2,7} /typeSafePets(?! )/
// my-integration/seed.ts
import { asDrizzleTable } from '@astrojs/db/utils';
import { db } from 'astro:db';
import { Pets } from './config';

export default async function() {
  const typeSafePets = asDrizzleTable('Pets', Pets);

  await db.insert(typeSafePets).values([
    { name: 'Palomita', species: 'cat' },
    { name: 'Pan', species: 'dog' },
  ]);
}
```

The value returned by `asDrizzleTable('Pets', Pets)` is equivalent to `import { Pets } from 'astro:db'`, but is available even when Astro’s type generation can’t run.
You can use it in any integration code that needs to query or insert into the database.
